package org.groupg.practice;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.WithItem;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.SetOperationList;

public class ComplexSqlParser {
    public static void main(String[] args) {
        String complexSql = "WITH regional_sales AS (\n" +
                "    SELECT \n" +
                "        region, \n" +
                "        SUM(amount) AS total_sales,\n" +
                "        COUNT(DISTINCT customer_id) AS unique_customers\n" +
                "    FROM orders\n" +
                "    WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'\n" +
                "    GROUP BY region\n" +
                "    HAVING SUM(amount) > 100000\n" +
                "),\n" +
                "top_regions AS (\n" +
                "    SELECT region\n" +
                "    FROM regional_sales\n" +
                "    WHERE total_sales > (SELECT AVG(total_sales) * 1.5 FROM regional_sales)\n" +
                ")\n" +
                "SELECT\n" +
                "    o.order_id,\n" +
                "    o.order_date,\n" +
                "    c.customer_name,\n" +
                "    c.country,\n" +
                "    p.product_name,\n" +
                "    p.category,\n" +
                "    (SELECT MAX(price) FROM products WHERE category = p.category) AS max_category_price,\n" +
                "    CASE \n" +
                "        WHEN o.amount > 1000 THEN 'Premium'\n" +
                "        WHEN o.amount BETWEEN 500 AND 1000 THEN 'Standard'\n" +
                "        ELSE 'Economy'\n" +
                "    END AS order_class,\n" +
                "    RANK() OVER (\n" +
                "        PARTITION BY c.country \n" +
                "        ORDER BY o.amount DESC\n" +
                "    ) AS country_sales_rank,\n" +
                "    o.amount * (1 - COALESCE(o.discount, 0)) AS net_amount,\n" +
                "    CONCAT(e.first_name, ' ', e.last_name) AS sales_rep,\n" +
                "    (\n" +
                "        SELECT STRING_AGG(comment, '; ') \n" +
                "        FROM order_comments oc \n" +
                "        WHERE oc.order_id = o.order_id\n" +
                "    ) AS order_comments\n" +
                "FROM orders o\n" +
                "JOIN customers c ON o.customer_id = c.customer_id\n" +
                "LEFT JOIN products p ON o.product_id = p.product_id\n" +
                "INNER JOIN employees e ON o.sales_rep_id = e.employee_id\n" +
                "JOIN top_regions tr ON c.region = tr.region\n" +
                "WHERE c.country IN ('USA', 'Canada', 'UK')\n" +
                "    AND p.category NOT LIKE '%Software%'\n" +
                "    AND o.status = 'completed'\n" +
                "    AND EXISTS (\n" +
                "        SELECT 1 \n" +
                "        FROM special_offers so \n" +
                "        WHERE so.product_id = p.product_id \n" +
                "            AND so.start_date <= o.order_date \n" +
                "            AND so.end_date >= o.order_date\n" +
                "    )\n" +
                "GROUP BY \n" +
                "    o.order_id, \n" +
                "    c.customer_name, \n" +
                "    c.country, \n" +
                "    p.product_name, \n" +
                "    p.category,\n" +
                "    e.first_name,\n" +
                "    e.last_name,\n" +
                "    o.amount,\n" +
                "    o.discount\n" +
                "HAVING SUM(o.quantity) > 5\n" +
                "    AND AVG(o.amount) > 200\n" +
                "ORDER BY \n" +
                "    c.country ASC,\n" +
                "    net_amount DESC NULLS LAST,\n" +
                "    order_date DESC\n" +
                "LIMIT 100\n" +
                "OFFSET 10\n" +
                "UNION ALL\n" +
                "SELECT \n" +
                "    order_id,\n" +
                "    order_date,\n" +
                "    'Legacy Customer' AS customer_name,\n" +
                "    'Unknown' AS country,\n" +
                "    'Discontinued Product' AS product_name,\n" +
                "    NULL AS category,\n" +
                "    0 AS max_category_price,\n" +
                "    'Archived' AS order_class,\n" +
                "    NULL AS country_sales_rank,\n" +
                "    amount AS net_amount,\n" +
                "    'System' AS sales_rep,\n" +
                "    'Migrated from old system' AS order_comments\n" +
                "FROM archived_orders\n" +
                "WHERE order_date < '2020-01-01';";

        try {
            Statement statement = CCJSqlParserUtil.parse(complexSql);

            if (statement instanceof Select select) {

                // 解析WITH子句
                if (select.getWithItemsList() != null) {
                    for (WithItem withItem : select.getWithItemsList()) {
                        System.out.println("CTE: " + withItem.getAliasName());
                    }
                }

                // 解析主体查询
                if (select.getSelectBody() instanceof SetOperationList) {
                    SetOperationList setOp = (SetOperationList) select.getSelectBody();
                    System.out.println("包含 " + setOp.getOperations().size() + " 个UNION操作");

                    // 处理第一个SELECT
                    PlainSelect firstSelect = (PlainSelect) setOp.getSelects().get(0);
                    processPlainSelect(firstSelect);

                    // 处理第二个SELECT
                    PlainSelect secondSelect = (PlainSelect) setOp.getSelects().get(1);
                    processPlainSelect(secondSelect);
                }
            }
        } catch (JSQLParserException e) {
            e.printStackTrace();
        }
    }

    private static void processPlainSelect(PlainSelect plainSelect) {
        System.out.println("\n解析SELECT语句:");
        System.out.println("FROM: " + plainSelect.getFromItem());

        // 处理JOIN
        if (plainSelect.getJoins() != null) {
            System.out.println("\nJOIN列表:");
            plainSelect.getJoins().forEach(join -> {
                System.out.println(" - " + join + " JOIN: " + join.getRightItem());
            });
        }

        // 处理WHERE
        if (plainSelect.getWhere() != null) {
            System.out.println("\nWHERE条件: " + plainSelect.getWhere());
        }

        // 处理GROUP BY
        if (plainSelect.getGroupBy() != null) {
            System.out.println("\nGROUP BY: " + plainSelect.getGroupBy());
        }

        // 处理HAVING
        if (plainSelect.getHaving() != null) {
            System.out.println("\nHAVING条件: " + plainSelect.getHaving());
        }

        // 处理ORDER BY
        if (plainSelect.getOrderByElements() != null) {
            System.out.println("\nORDER BY:");
            plainSelect.getOrderByElements().forEach(order -> {
                System.out.println(" - " + order.getExpression() +
                                  " " + order.isAsc() +
                                  " NULLS " + order.getNullOrdering());
            });
        }

        // 处理LIMIT/OFFSET
        if (plainSelect.getLimit() != null) {
            System.out.println("\nLIMIT: " + plainSelect.getLimit().getRowCount() +
                               ", OFFSET: " + plainSelect.getOffset().getOffset());
        }
    }
}
